package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Department;
import ModelLayer.Employee;

public class DBDepartment implements IFDBDepartment {

    private  Connection con;

    public DBDepartment() {
        con = DbConnection.getInstance().getDBcon();
    }

    @Override
    public ArrayList<Department> getAllDepartment(boolean retriveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public Department findDepartment(int dno, boolean retriveAssociation) {
        return singleWhere("dnumber = " + dno, false);
    }

    @Override
    public Department searchDeparmentOnName(String dname, boolean retriveAssociation) {
        String wClause = "dname like  '%" + dname + "%'";
        System.out.println("Search department " + wClause);
        return singleWhere(wClause, false);
    }

    @Override
    public int insert(Department dept) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int update(Department dept) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int delete(Department dept) {
        // TODO Auto-generated method stub
        return 0;
    }

    //Singelwhere is used when we only select one department
    private Department singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Department depObj = new Department();

        String query =  buildQuery(wClause);
        System.out.println(query);

        try{ // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                depObj = buildDepartment(results);
                //assocaition is to be build
                stmt.close();

            }
            else{ //no employee was found
                depObj = null;
            }
        }//end try
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return depObj;
    }

    private ArrayList<Department> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Department> list = new ArrayList<Department>();

        String query =  buildQuery(wClause);

        try {
            // read the employee from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while( results.next() ){
                Department empObj = new Department();
                empObj = buildDepartment(results);
                list.add(empObj);
            }

            stmt.close();
        }

        catch(Exception e){
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    //method to build the query
    private String buildQuery(String wClause) {
        String query="SELECT dname, dnumber, mgrssn, mgrstartdate FROM department";

        if (wClause.length()>0) {
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    //method to build an employee object
    private Department buildDepartment(ResultSet results) {
        Department depObj = new Department();
        try { // the columns from the table department are used
            depObj.setDname(results.getString("dname"));
            depObj.setDnumber(results.getInt("dnumber"));
            depObj.setManager(new Employee(results.getString("mgrssn")));
            depObj.setStartdato(results.getString("mgrstartdate"));
        }
        catch(Exception e) {
            System.out.println("error in building the department object");
        }
        return depObj;
    }
}
